package com.h3bpm.web.utils;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import static org.apache.poi.hssf.record.ExtendedFormatRecord.VERTICAL_CENTER;

public class StatementUntil {
    public static final String PROJECT_PATH = System.getProperty("user.dir");

    public static final String RESOURCES_PATH = File.separator + "src" + File.separator + "main" + File.separator + "resources" + File.separator + "excelPath";

    public static final String CONTRACT_STATEMENT_NAME = "test.xlsx";


    /**
     * 生成Excel
     *
     * @throws IOException
     */
    public static XSSFWorkbook generateExcel()throws Exception {
        //创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建表单
        XSSFSheet sheet = genSheet(workbook, "sheet1");
        /**
         *冻结行列
         *cellNum:表示要冻结的列数；
         *rowNum:表示要冻结的行数；
         *firstCellNum:表示被固定列右边第一列的列号；
         *firstRollNum :表示被固定行下边第一列的行号;
         */
        sheet.createFreezePane(4, 6, 4, 6);
        //创建表单样式
        //创建标题样式
        XSSFCellStyle titleStyle = genTitleStyle(workbook);
        //创建文本样式
        XSSFCellStyle contextStyle = genContextStyle(workbook);
        //创建Excel
        genExcel(sheet, titleStyle, contextStyle,workbook);

        return workbook;
    }

    /**
     * 合并单元格
     * firstRow 第一行
     * lastRow  最后一行
     * firstCol 第一列
     * lastCol 最后一列
     * lastRow > firstRow || lastCol < firstCol
     * CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
     * 设置生成自定义Excel表头
     * @param sheet        表单
     * @param titleStyle   标题样式
     * @param contextStyle 文本样式
     */
    public static void genExcel(XSSFSheet sheet, XSSFCellStyle titleStyle, XSSFCellStyle contextStyle,XSSFWorkbook workbook) {
        XSSFCellStyle contextGroundStyle = genGroundContextStyle(workbook);
        setCellWidth(sheet);
        //设置标题位置
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        //创建第一行，为标题，index从0开始
        XSSFRow row = sheet.createRow(0);
        //设置第一行Height高度
        row.setHeightInPoints(30);
        XSSFCell cell;
        //创建第一列
        cell = row.createCell(0);
        //标题
        cell.setCellValue("公司合同管理汇总报表");
        //设置标题样式，放在赋值之后生效
        cell.setCellStyle(titleStyle);
        //创建第二行
        row = sheet.createRow(1);
        //第二行的合并单元格
        CellRangeAddress regionRow1_1_0 = new CellRangeAddress(1, 1, 0, 1);
        CellRangeAddress regionRow1_1_1 = new CellRangeAddress(1, 1, 2, 4);
        CellRangeAddress regionRow1_1_2 = new CellRangeAddress(1, 1, 5, 7);
        CellRangeAddress regionRow1_1_3 = new CellRangeAddress(1, 1, 8, 12);
        genCellRangerStyle(regionRow1_1_0,sheet);
        genCellRangerStyle(regionRow1_1_1,sheet);
        genCellRangerStyle(regionRow1_1_2,sheet);
        genCellRangerStyle(regionRow1_1_3,sheet);
        cell = row.createCell(0);
        cell.setCellValue("报送单位");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(5);
        cell.setCellValue("报送期间");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("                     年            季");
        cell.setCellStyle(contextStyle);
        // 第三行
        row = sheet.createRow(2);
        CellRangeAddress region2_2_0 = new CellRangeAddress(2, 2, 0, 1);
        CellRangeAddress region2_2_1 = new CellRangeAddress(2, 2, 2, 12);
        genCellRangerStyle(region2_2_0,sheet);
        genCellRangerStyle(region2_2_1,sheet);
        cell = row.createCell(0);
        cell.setCellValue("报送单位类别");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(2);
        cell.setCellValue("合同承办单位（       ）        合同法人授权签订单位（        ）");
        cell.setCellStyle(contextStyle);
        // 第四行
        row = sheet.createRow(3);
        CellRangeAddress region3_3_0 = new CellRangeAddress(3, 3, 8, 11);
        genCellRangerStyle(region3_3_0,sheet);
        cell = row.createCell(0);
        cell.setCellValue("合同签订期间");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(2);
        cell.setCellValue("合同类别（按类别填报）");
        cell.setCellStyle(contextGroundStyle);
        //新加
        cell = row.createCell(3);
        cell.setCellValue("合同性质");
        cell.setCellStyle(contextGroundStyle);

        cell = row.createCell(4);
        cell.setCellValue("新签订");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(6);
        cell.setCellValue("正常履约完成合同（截至目前所有）");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("超期限合同（截至目前所有）");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(12);
        cell.setCellValue("备注");
        cell.setCellStyle(contextGroundStyle);
        // 第五行
        row = sheet.createRow(4);
        CellRangeAddress region3_4_0 = new CellRangeAddress(3, 4, 4, 5);
        CellRangeAddress region3_4_1 = new CellRangeAddress(3, 4, 6, 7);
        CellRangeAddress region4_4_0 = new CellRangeAddress(4, 4, 8, 9);
        CellRangeAddress region4_4_1 = new CellRangeAddress(4, 4, 10, 11);
        genCellRangerStyle(region3_4_0,sheet);
        genCellRangerStyle(region3_4_1,sheet);
        genCellRangerStyle(region4_4_0,sheet);
        genCellRangerStyle(region4_4_1,sheet);
        cell = row.createCell(8);
        cell.setCellValue("履约完成");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(10);
        cell.setCellValue("仍未履约");
        cell.setCellStyle(contextGroundStyle);
        // 第六行
        row = sheet.createRow(5);
        CellRangeAddress region3_5_0 = new CellRangeAddress(3, 5, 0, 1);
        CellRangeAddress region3_5_1 = new CellRangeAddress(3, 5, 2, 2);
        CellRangeAddress region3_5_2 = new CellRangeAddress(3, 5, 3, 3);
        CellRangeAddress region3_5_3 = new CellRangeAddress(3, 5, 12, 12);
        genCellRangerStyle(region3_5_0,sheet);
        genCellRangerStyle(region3_5_1,sheet);
        genCellRangerStyle(region3_5_2,sheet);
        genCellRangerStyle(region3_5_3,sheet);
        cell = row.createCell(4);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(5);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(6);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(7);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(9);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(10);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(11);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);

        genSheetDate(sheet,contextStyle);
    }

    /**
     * 设置Excel 的列宽
     * @param sheet
     */
    private static void setCellWidth(XSSFSheet sheet){
        //根据Excel列名长度，指定列名宽度  Excel总共12列
        for (int i = 0; i <= 12; i++) {
            if (i == 0) {
                sheet.setColumnWidth(i, 1500);
            } else if (i == 1) {
                sheet.setColumnWidth(i, 8000);
            } else if (i == 2) {
                sheet.setColumnWidth(i, 3500);
            } else if (i == 3) {
                sheet.setColumnWidth(i, 3500);
            } else if (i == 4 || i == 6 || i == 8 || i == 10) {
                sheet.setColumnWidth(i, 2000);
            } else if (i == 5 || i == 7 || i == 9 || i == 11) {
                sheet.setColumnWidth(i, 3000);
            }else if (i == 12 ){
                sheet.setColumnWidth(i, 7000);
            }
        }
    }

    /**
     * 实际填充数据的时候，对可能为空的数据要进行处理，要先进行判断，否则报错
     *   String value = Test.getVal();
     *   if(StringUtils.isNotBlank(value)) {
     *   cell.setCellValue(value);
     *  } else{
     *   cell.setCellValue(" ");
     *  }
     * 从数据库取数据填充到Excel
     * 填充Excel数据
     * @param sheet
     * @param contextStyle
     */
    public static void genSheetDate(XSSFSheet sheet, XSSFCellStyle contextStyle) {
        XSSFCell cell;
        XSSFRow row;
        int cellNum =27;
        /**
         * i从6开始计数，因为上面已经创建了 0 1 2 3 4 5行
         */
        for (int i = 6; i < cellNum; i++) {
            //创建第三行
            row = sheet.createRow(i);
            //创建第三行第一列
            cell = row.createCell(0);
            //第三行第一列的值
            cell.setCellValue("测试第1列"+"第"+i+"行");
            //设置样式
            cell.setCellStyle(contextStyle);
            //2 row 1 column
            cell = row.createCell(1);
            cell.setCellValue("测试第2列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 2 column
            cell = row.createCell(2);
            cell.setCellValue("测试第3列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 3 column
            cell = row.createCell(3);
            cell.setCellValue("测试第4列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 4 column
            cell = row.createCell(4);
            cell.setCellValue("测试第5列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 5 column
            cell = row.createCell(5);
            cell.setCellValue("测试第6列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 6 column
            cell = row.createCell(6);
            cell.setCellValue("测试第7列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 7 column
            cell = row.createCell(7);
            cell.setCellValue("测试第8列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 8 column
            cell = row.createCell(8);
            cell.setCellValue("测试第9列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            //2 row 9 column
            cell = row.createCell(9);
            cell.setCellValue("测试第10列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            cell = row.createCell(10);
            cell.setCellValue("测试第11列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            cell = row.createCell(11);
            cell.setCellValue("测试第12列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
            cell = row.createCell(12);
            cell.setCellValue("测试第12列"+"第"+i+"行");
            cell.setCellStyle(contextStyle);
        }
        genSheetEndnote(sheet,contextStyle,cellNum);
    }

    /**
     * 设置列表的尾部
     * @param sheet
     * @param contextStyle
     */
    public static void genSheetEndnote(XSSFSheet sheet, XSSFCellStyle contextStyle, int cellNum) {
        XSSFCell cell;
        XSSFRow row;
        Date date = new Date();
        row = sheet.createRow(cellNum);
        CellRangeAddress region26_26_0 = new CellRangeAddress(cellNum, cellNum, 1, 12);
        genCellRangerStyle(region26_26_0, sheet);
        cell = row.createCell(1);
        cell.setCellValue("需要反馈的事项：");
        cell.setCellStyle(contextStyle);
        cell = row.createCell(0);
        cell.setCellValue("四");
        cell.setCellStyle(contextStyle);
        //27
        row = sheet.createRow(cellNum + 1);
        cell = row.createCell(1);
        cell.setCellValue("……（本单位合同管理相关情况：包括但不限于合同签订、合同履行、合同违约、纠纷、诉讼等需要公司层面协调事项等方面。）");
        cell.setCellStyle(contextStyle);
        //28
        row = sheet.createRow(cellNum + 2);
        //29
        row = sheet.createRow(cellNum + 3);
        cell = row.createCell(1);
        cell.setCellValue("备注：(1)上表灰色区域为不使用，不需要填写。(2)本表请于每季度末的下一个月5个工作日内报送。（3）本表一式两份，一份报审计和风险部（法律事务部），一份自存。");
        //30
        row = sheet.createRow(cellNum + 4);
        CellRangeAddress region26_28_0 = new CellRangeAddress(cellNum, cellNum + 2, 0, 0);
        CellRangeAddress region27_28_0 = new CellRangeAddress(cellNum + 1, cellNum + 2, 1, 12);
        CellRangeAddress region29_29_0 = new CellRangeAddress(cellNum + 3, cellNum + 3, 1, 12);

        genCellRangerStyle(region26_28_0, sheet);
        genCellRangerStyle(region27_28_0, sheet);
        sheet.addMergedRegion(region29_29_0);
        cell = row.createCell(1);
        cell.setCellValue("单位主管（签字）：");
        cell = row.createCell(3);
        cell.setCellValue("审核：");
        cell = row.createCell(6);
        cell.setCellValue("填报：");
        cell = row.createCell(11);
        cell.setCellValue("填报日期：");
        cell = row.createCell(12);
        String format = DateFormatUtils.format(date, "yyyy-MM-dd hh:mm:ss");
        System.out.println("填报时间： "+format);
        cell.setCellValue(format);
    }

    private static void genCellRangerStyle(CellRangeAddress cellAddresses,XSSFSheet sheet){
        sheet.addMergedRegion(cellAddresses);
        RegionUtil.setBorderTop(1, cellAddresses, sheet);
        RegionUtil.setBorderBottom(1, cellAddresses, sheet);
        RegionUtil.setBorderLeft(1, cellAddresses, sheet);
        RegionUtil.setBorderRight(1, cellAddresses, sheet);
    }
    /**
     * 设置表单，并生成表单
     *
     * @param workbook  工作簿
     * @param sheetName sheet名字
     * @return
     */
    public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) {
        //生成表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表单文本居中
        sheet.setHorizontallyCenter(true);
        sheet.setFitToPage(false);
        //打印时在底部右边显示文本页信息
        Footer footer = sheet.getFooter();
        footer.setRight("Page " + org.apache.poi.hssf.usermodel.HeaderFooter.numPages() + " Of " + org.apache.poi.hssf.usermodel.HeaderFooter.page());
        //打印时在头部右边显示Excel创建日期信息
        Header header = sheet.getHeader();
        header.setRight("Create Date " + org.apache.poi.hssf.usermodel.HeaderFooter.date() + " " + HeaderFooter.time());
        //设置打印方式
        XSSFPrintSetup ps = sheet.getPrintSetup();
        // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
        ps.setLandscape(true);
        //打印尺寸大小设置为A4纸大小
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        return sheet;
    }


    /**
     * 创建文本样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle genContextStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        //文本水平居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //文本自动换行
        style.setWrapText(true);

        //生成Excel表单，需要给文本添加边框样式和颜色
        /*
             CellStyle.BORDER_DOUBLE      双边线
             CellStyle.BORDER_THIN        细边线
             CellStyle.BORDER_MEDIUM      中等边线
             CellStyle.BORDER_DASHED      虚线边线
             CellStyle.BORDER_HAIR        小圆点虚线边线
             CellStyle.BORDER_THICK       粗边线
         */
        //设置文本边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //set border color
        //设置文本边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    public static XSSFCellStyle genGroundContextStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        //文本水平居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //文本自动换行
        style.setWrapText(true);

        //生成Excel表单，需要给文本添加边框样式和颜色
        /*
             CellStyle.BORDER_DOUBLE      双边线
             CellStyle.BORDER_THIN        细边线
             CellStyle.BORDER_MEDIUM      中等边线
             CellStyle.BORDER_DASHED      虚线边线
             CellStyle.BORDER_HAIR        小圆点虚线边线
             CellStyle.BORDER_THICK       粗边线
         */
        //设置文本边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //set border color
        //设置文本边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置前景填充样式
        style.setFillPattern(FillPatternType.BIG_SPOTS);
        //前景填充色
        style.setFillBackgroundColor(IndexedColors.SEA_GREEN.getIndex());
        style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        return style;
    }

    /**
     * 设置生成标题样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        //标题居中，没有边框，所以这里没有设置边框，设置标题文字样式
        XSSFFont titleFont = workbook.createFont();
        //加粗
        titleFont.setBold(true);
        //文字尺寸
        titleFont.setFontHeight((short) 15);
        titleFont.setFontHeightInPoints((short) 15);
        style.setFont(titleFont);
        return style;
    }
}
